package com.glasses.manage.export.importExcel;

import jxl.*;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <li>Title:Excel导入工具类</li>
 */
public class ImportExcelUtil {


    /**
     * 从excel文件中读取内容 以第一行为key，从第二行开始为正式数据
     *
     * @param file
     * @throws BiffException
     * @throws IOException
     */
    public static final Map<String, List<Map<String, String>>> readSheets(File file) {
        Map<String, List<Map<String, String>>> map = null;
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(file);
            String[] sheetNames = workbook.getSheetNames();
            if (sheetNames != null) {
                map = new HashMap<String, List<Map<String, String>>>();
                for (String sheetName : sheetNames) {
                    map.put(sheetName, readSheet(workbook, sheetName));
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null)
                workbook.close();
        }
        return map;
    }


    /**
     * 从excel文件中读取内容 以第一行为key，从第二行开始为正式数据
     *
     * @param file
     * @throws BiffException
     * @throws IOException
     */
    public static final List<Map<String, String>> readSheet(File file) {
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(file);
            return readSheet(workbook, null);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null)
                workbook.close();
        }
        return null;
    }


    /**
     * 从excel文件中读取内容 以第一行为key，从第二行开始为正式数据
     *
     * @throws BiffException
     * @throws IOException
     */
    public static final List<Map<String, String>> readSheet(Workbook workbook, String sheetName) {
        List<Map<String, String>> result = null;
        Sheet sheet = null;
        try {
            if (sheetName == null) {
                sheet = workbook.getSheet(0);
            } else {
                sheet = workbook.getSheet(sheetName);
            }
            int rowSize = sheet.getRows();
            if (rowSize != 0) {
                Cell[] headCell = sheet.getRow(0);
                result = new ArrayList<Map<String, String>>(rowSize - 1);
                Cell[] cells;
                for (int i = 1; i < rowSize; i++) {
                    cells = sheet.getRow(i);
                    Map<String, String> data = new HashMap<String, String>();
                    for (int j = 0; j < headCell.length; j++) {
                        if (cells.length > j) {
                            if (cells[j].getType() == CellType.DATE) { // TODO:时间类型读取
                                DateCell dc = (DateCell) cells[j];
                                Date date = dc.getDate();
                                TimeZone zone = TimeZone.getTimeZone("GMT");
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                sdf.setTimeZone(zone);
                                String dateStr = sdf.format(date);
                                data.put(trim(headCell[j].getContents()), dateStr);
                            } else if (cells[j].getType() == CellType.NUMBER) { // 数字类型读取
                                if (cells[j].getContents().indexOf(".") > 0) {
                                    NumberCell nc = (NumberCell) cells[j];
                                    double value = nc.getValue();
                                    data.put(trim(headCell[j].getContents()), trim(String.valueOf(value)));
                                } else {
                                    data.put(trim(headCell[j].getContents()), trim(cells[j].getContents()));
                                }
                            } else {
                                data.put(trim(headCell[j].getContents()), trim(cells[j].getContents()));
                            }
                        }
                    }
                    result.add(data);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }


    private static final String trim(String str) {
        return str == null ? "" : str.trim();
    }
}
